﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="InstrumentContainerControl.cs" company="">
//   
// </copyright>
// <summary>
//   The instrument container control.
// </summary>
// --------------------------------------------------------------------------------------------------------------------


using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Threading;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using Systemathics.FrameWork;
using Systemathics.UserControls.Properties;
using Application = Microsoft.Office.Interop.Excel.Application;
using MenuItem = System.Windows.Forms.MenuItem;

namespace Systemathics.UserControls
{
    /// <summary>
    /// The instrument container control.
    /// </summary>
    [ToolboxBitmap(typeof (InstrumentContainerControl), "UserControlsIcon.bmp")]
    public sealed partial class InstrumentContainerControl : UserControl
    {
        private readonly List<Guid> InstrumentsToRecord;
        private readonly Image signal;
        private readonly Image signal_5;
        private readonly Bitmap Start;
        private readonly Bitmap Stop;
        private bool check_Status;
        private DataService m_DataService;
        private Recorder Recorder;
        private RemotingLogClient Remoter;

        private readonly SynchronizationContext synchronizationContext;
        private readonly SendOrPostCallback StatusChangedOnRecorderEvenDelegate;
        private readonly SendOrPostCallback StatusChangedOnConnectionEventDelegate;

        public InstrumentContainerControl()
        {
            InitializeComponent();
            DoubleBuffered = true;

            signal = Image.FromHbitmap(Resources.Signal.GetHbitmap());
            signal_5 = Image.FromHbitmap(Resources.Signal_5.GetHbitmap());
            Thread.CurrentThread.CurrentCulture = GlobalVariables.SystemathicsCulture();
            check_Status = false;
            Start = Resources.quick_restart;
            Stop = Resources.exit;
            RecorderToolStripButton.CheckOnClick = true;
            RecorderToolStripButton.CheckStateChanged += RecorderToolStripButtonCheckStateChanged;
            InstrumentsToRecord = new List<Guid>();
            synchronizationContext = SynchronizationContext.Current;
            StatusChangedOnRecorderEvenDelegate = new SendOrPostCallback(StatusChangedOnRecorderEvent);
            StatusChangedOnConnectionEventDelegate = new SendOrPostCallback(StatusChangedOnConnectionEvent);
            Remoter = new RemotingLogClient();

        }

        public DataService DataService
        {
            get { return m_DataService; }
            set
            {
                m_DataService = value;
                m_DataService.EmitConnectionStatus += DataProviderEmitConnectionStatus;

                if (m_DataService.isConnected)
                    StatusChangedOnConnectionEvent(new FireMessageEventArgs(ConnectionStatus.Connected, Time.Now.TimeOfDay));
            }
        }
        public void MenuItemClick(object sender, EventArgs e)
        {
            notifyIcon1.Dispose();
            if (ParentForm != null) ParentForm.Close();
        }

        private void InstrumentContainerControl_Load(object sender, EventArgs e)
        {
            Dock = DockStyle.Fill;
            if (ParentForm != null)
            {
                ParentForm.Name = "Instruments Container";
                ParentForm.Icon = Icon.FromHandle(Resources.Buddy_bleu.GetHicon());
                ParentForm.ShowIcon = true;
                ParentForm.SizeGripStyle = SizeGripStyle.Hide;
                ParentForm.MaximizeBox = true;
                ParentForm.MinimizeBox = true;
                ParentForm.StartPosition = FormStartPosition.CenterScreen;
                ParentForm.FormBorderStyle = FormBorderStyle.Sizable;
                ParentForm.ControlBox = true;
                ParentForm.Opacity = 0.9;

                ParentForm.FormClosing += ParentFormFormClosing;
                ParentForm.Resize += ParentFormResize;
            }
            var exitItem = new MenuItem {Index = 0, Text = "E&xit Instruments Container"};
            var borderItem = new MenuItem {Index = 0, Text = "H&ide/Show Borders"};
            borderItem.Click += BorderItemClick;
            exitItem.Click += MenuItemClick;

            var menu = new ContextMenu();
            menu.MenuItems.Add(borderItem);
            menu.MenuItems.Add(exitItem);

            notifyIcon1.Icon = Icon.FromHandle(Resources.Buddy_bleu.GetHicon());
            notifyIcon1.ContextMenu = menu;
            notifyIcon1.Text = "Instruments Container";
            notifyIcon1.Visible = true;
            notifyIcon1.Click += NotiftyIconClick;

            notifyIcon1.ShowBalloonTip(1000);

            using (var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()))
                dBInstrumentsBindingSource.DataSource = db.DBInstruments.Execute(MergeOption.OverwriteChanges);
        }
        private void BorderItemClick(object sender, EventArgs e)
        {
            if (ParentForm != null)
                if (ParentForm.FormBorderStyle == FormBorderStyle.Sizable)
                {
                    ParentForm.FormBorderStyle = FormBorderStyle.None;
                    ParentForm.ControlBox = false;
                }
                else
                {
                    ParentForm.FormBorderStyle = FormBorderStyle.Sizable;
                    ParentForm.ControlBox = true;
                }
        }
        private void NotiftyIconClick(object sender, EventArgs e)
        {
            if (ParentForm != null)
            {
                if (ParentForm.WindowState == FormWindowState.Minimized)
                {
                    ParentForm.Show();
                    ParentForm.WindowState = FormWindowState.Normal;
                }

                ParentForm.Activate();
            }
        }
        private void ParentFormResize(object sender, EventArgs e)
        {
            if (ParentForm != null)
                if (ParentForm.WindowState == FormWindowState.Minimized)
                    ParentForm.Hide();
        }
        private void ParentFormFormClosing(object sender, FormClosingEventArgs e)
        {
            if (m_DataService != null)
                m_DataService.EmitConnectionStatus -= DataProviderEmitConnectionStatus;
            notifyIcon1.Dispose();
        }
        private void ListToRecordtoolStripButton_Click(object sender, EventArgs e)
        {
            openFileDialog.Title = " Select a List of Instruments To Record";
            openFileDialog.Filter = "text (*.txt)|*.txt";
            openFileDialog.InitialDirectory = GlobalVariables.RESOURCES;

            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                if (!String.IsNullOrEmpty(openFileDialog.FileName))
                {
                    var sr = new StreamReader(openFileDialog.FileName);
                    var AssetName = String.Empty;
                    while ((AssetName = sr.ReadLine()) != null)
                    {
                        foreach (DataGridViewRow dr in InstrumentsDataGridView.Rows)
                        {
                            var dbi = (DBInstruments) dr.DataBoundItem;
                            if (dbi.Name == AssetName)
                                dr.Cells[0].Value = true;
                        }
                    }
                    sr.Close();
                    InstrumentsDataGridView.Invalidate();
                    InstrumentsDataGridView.Update();
                }
            }
        }
        private void RecorderToolStripButtonCheckStateChanged(object sender, EventArgs e)
        {
            if (IsHandleCreated)
            {
                var bt = sender as ToolStripButton;
                if (bt.CheckState == CheckState.Checked)
                {
                    InstrumentsToRecord.Clear();
                    var earliestTime = TimeSpan.MaxValue;
                    var latestTime = TimeSpan.MinValue;

                    if (InstrumentsDataGridView.Rows.Count > 0) 
                    {
                        foreach (DataGridViewRow dr in InstrumentsDataGridView.Rows)
                        {
                            if (dr.Cells[0].FormattedValue.Equals(true))
                            {
                                var id = (Guid)dr.Cells[1].Value;
                                InstrumentsToRecord.Add(id);
                            }
                        }
                        using (var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()))
                        {
                            foreach (Guid id in InstrumentsToRecord)
                            {
                                db.DBInstruments.Where(u => u.ID == id).FirstOrDefault().DBExchangeReference.Load();
                                var openAuctions = db.DBInstruments.Where(u => u.ID == id).FirstOrDefault().DBExchange.OpenAuctions;
                                var closeAuctions = db.DBInstruments.Where(u => u.ID == id).FirstOrDefault().DBExchange.CloseAuctions;

                                if (openAuctions < earliestTime) earliestTime = openAuctions;
                                if (closeAuctions > latestTime) latestTime = closeAuctions;
                            }
                        }
                        if (InstrumentsToRecord.Count > 0 && DataService.isConnected)
                        {
                            if(Recorder == null)
                            {
                                Recorder = new Recorder(DataService, DateTime.Today.AddDays(-1) + earliestTime, DateTime.Today + latestTime, InstrumentsToRecord);
                                Recorder.RecorderStatus += RecorderRecorderStatus;
                                LaunchRecording();
                            }
                        }
                        else
                            StatusStrip.Text = " Please Select at least one instrument";
                        //RecorderToolStripButton.Checked = true;
                        RecorderToolStripButton.CheckState = CheckState.Indeterminate;
                    }
                }
                else if (bt.CheckState == CheckState.Unchecked)
                {
                    if (Recorder != null)
                        StopRecording();
                    RecorderToolStripButton.Checked = false;
                }
            }
        }
        
        private void DataProviderEmitConnectionStatus(object sender, FireMessageEventArgs e)
        {
            if (IsHandleCreated)
                synchronizationContext.Post(StatusChangedOnConnectionEventDelegate, e);
        }
        private void RecorderRecorderStatus(object sender, FireMessageEventArgs e)
        {
            synchronizationContext.Post(StatusChangedOnRecorderEvenDelegate, e);
        }
        private void StatusChangedOnRecorderEvent(Object o)
        {
            var e = o as FireMessageEventArgs;
            var cs = (ConnectionStatus) e.Message;
            switch (cs)
            {
                case ConnectionStatus.Connected:
                    StatusStrip.Text = "Recording Started";
                    if (Remoter != null)
                    {
                        if (Recorder.Status == ConnectionStatus.Disconnected)
                            Remoter.Trace(new FireMessageEventArgs("Recorder Started", Time.Now.TimeOfDay));
                        //foreach (Guid g in InstrumentsToRecord)
                        //    RemotingClient.Trace(new FireMessageEventArgs("Recording :" + Instrument.GetInstrument(g).StaticData.Name, DateTime.Now.TimeOfDay));
                    }

                    break;
                case ConnectionStatus.Disconnected:
                    StatusStrip.Text = "Recording stopped";
                    if (Remoter != null)
                        Remoter.Trace(new FireMessageEventArgs("Recorder stopped", Time.Now.TimeOfDay));
                    break;
                default:
                    break;
            }
        }
        private void StatusChangedOnConnectionEvent(Object o)
        {
            var e = o as FireMessageEventArgs;
            var cs = (ConnectionStatus) e.Message;
            switch (cs)
            {
                case ConnectionStatus.Connected:
                    RecorderToolStripButton.Enabled = true;
                    UpdateInstrumentsAndCrncytoolStripButton.Enabled = true;

                    ToolStripStatusLabel.Image = signal_5;
                    LaunchRecording();
                    break;
                case ConnectionStatus.Disconnected:
                    RecorderToolStripButton.Enabled = false;
                    UpdateInstrumentsAndCrncytoolStripButton.Enabled = false;
                    ToolStripStatusLabel.Image = signal;
                    StopRecording();
                    break;
                default:
                    break;
            }
        }
        private void LaunchRecording()
        {
            if (InstrumentsToRecord.Count > 0)
            {
                RecorderToolStripButton.Image = Start;
                Recorder.Start();
                InstrumentsDataGridView.Columns["Rec"].Visible = false;
            }
        }
        private void StopRecording()
        {
            if (InstrumentsToRecord.Count > 0)
            {
                RecorderToolStripButton.Image = Stop;
                Recorder.Stop();
                InstrumentsDataGridView.Columns["Rec"].Visible = true;
            }
        }
        private void ExcelItem_Click(object sender, EventArgs e)
        {
            Thread.CurrentThread.CurrentCulture = GlobalVariables.SystemathicsCulture();
            using (var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()))
            {
                var list = new List<List<String>>();
                list.Add(new List<string>(new[]
                                                 {
                                                     "ID",
                                                     "BelongToIndex",
                                                     "Capitalization",
                                                     "Classe",
                                                     "Sector",
                                                     "Type",
                                                     "Country",
                                                     "Currency",
                                                     "Exchange",
                                                     "ISIN",
                                                     "Maturity",
                                                     "Name",
                                                     "isBuyAllowed",
                                                     "isHardToBorrow",
                                                     "isSellAllowed",
                                                     "isShortable",
                                                     "MaxNominalOrder",
                                                     "Tick",
                                                     "TickValue"
                                                 }));

                foreach (var i in db.DBInstruments.Execute(MergeOption.AppendOnly))
                {
                    i.DBCurrencyReference.Load();
                    i.DBExchangeReference.Load();
                    i.DBRulesReference.Load();
                    list.Add(new List<string>(new[]
                                                 {
                                                     i.ID.ToString(),
                                                     i.BelongToIndex,
                                                     i.Capitalization.ToString(),
                                                     i.Classe,
                                                     i.Sector,
                                                     i.Type,
                                                     i.Country,
                                                     i.DBCurrency.Ticker,
                                                     i.DBExchange.Exchange,
                                                     i.ISIN,
                                                     i.Maturity.ToString(),
                                                     i.Name,
                                                     i.DBRules.isBuyAllowed.ToString(),
                                                     i.DBRules.isHardToBorrow.ToString(),
                                                     i.DBRules.isSellAllowed.ToString(),
                                                     i.DBRules.isShortable.ToString(),
                                                     i.DBRules.MaxNominalOrder.ToString(),
                                                     i.DBRules.Tick.ToString(),
                                                     i.DBRules.TickValue.ToString(),
                                                 }));
                }

                var oe = new ToExcel("Dictionary");
                oe.WorkSheetName = "Instruments"; 
                oe.Add(list);
                list.Clear();

                list.Add(new List<string>(new[]
                                                 {
                                                    "Instrument",
                                                     "Provider",
                                                     "Ticker"
                                                 }));

                foreach (var i in db.DBTickers.Execute(MergeOption.AppendOnly))
                    list.Add(new List<string>(new[]
                                                 {
                                                     i.DBInstruments.ID.ToString(),
                                                     i.Provider,
                                                     i.Ticker
                                                 }));

                oe.WorkSheetName = "Tickers";
                oe.Add(list);
                list.Clear();

                list.Add(new List<string>(new[]
                                                 {
                                                   "Broker",
                                                   "Name",
                                                   "isPercentageFees",
                                                   "ExecFees",
                                                   "MinExecFee",
                                                   "CancelFees",
                                                   "ModifyFees",
                                                   "Borrow",
                                                   "Lend",
                                                   "Crncy",
                                                   "Intraday Initial Margin",
                                                   "Intraday Maintenance Margin",
                                                   "Overnight Initial Margin",
                                                   "Overnight Maintenance Margin"
                                                 }));
            
                foreach (var i in db.DBFees.Execute(MergeOption.AppendOnly))
                {
                    i.DBInstrumentsReference.Load();
                    list.Add(new List<string>(new[]
                                               {
                                                   i.Broker,
                                                   i.DBInstruments.Name,
                                                   i.isPercentageFees.ToString(),
                                                   i.ExecFees.ToString(),
                                                   i.MinExecFee.ToString(),
                                                   i.CancelFees.ToString(),
                                                   i.ModifyFees.ToString(),
                                                   i.Borrow.ToString(),
                                                   i.Lend.ToString(),
                                                   i.Crncy,
                                                   i.IntradayInitialMargin.ToString(),
                                                   i.IntradayMaintenanceMargin.ToString(),
                                                   i.OvernightInitialMargin.ToString(),
                                                   i.OvernightMaintenanceMargin.ToString()
                                                 }));
                }

                oe.WorkSheetName = "Fees";
                oe.Add(list);
                list.Clear();

                list.Add(new List<string>(new[]
                                                 {
                                                   "Instrument",
                                                   "OstDate",
                                                   "OstEvent",
                                                   "OstValue"
                                                 }));

                foreach (var i in db.DBOst.Execute(MergeOption.AppendOnly))
                {
                    i.DBInstrumentsReference.Load();
                    list.Add(new List<string>(new[]
                                               {
                                                   i.DBInstruments.ID.ToString(),
                                                   i.OstDate.ToString(),
                                                   i.OstEvent,
                                                   i.OstValue.ToString()}));
                }

                oe.Flush();
                oe.Close();
            }
            
        }
        private void DataBaseLocation_Click(object sender, EventArgs e)
        {
            Process.Start(GlobalVariables.DB);
        }
        private void UpdateInstrumentsAndCrncytoolStripButton_Click(object sender, EventArgs e)
        {
            if (m_DataService.isConnected)
            {
                m_DataService.UpdateInstruments();
                m_DataService.UpdateCurrencies();
            }
            RefreshDB(new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()));
        }
        private void SetInstrument(PropertyGrid propertyGrid)
        {
            using (var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()))
            {
                var i = propertyGrid.SelectedObject as OverridedInstrument;
                var instr = new DBInstruments();
                if (i.ExchangeName != null && i.Name != null && i.Currency != null)
                {
                    if (i.ID == null)
                    {
                        instr.ID = Guid.NewGuid();
                        instr.DBRules = new DBRules { ID = Guid.NewGuid() };
                        instr.DBTickers = new EntityCollection<DBTickers>();
                        instr.DBOst = new EntityCollection<DBOst>();
                        instr.DBFees = new EntityCollection<DBFees>();
                    }
                    else
                    {
                        instr = db.DBInstruments.Where(p => p.ID.CompareTo((Guid)i.ID) == 0).FirstOrDefault();
                        instr.DBTickers.Load();
                        foreach (DBTickers t in instr.DBTickers.Take(instr.DBTickers.Count).ToArray())
                        {
                            instr.DBTickers.Remove(t);
                            db.DeleteObject(t);
                        }

                        instr.DBOst.Load();
                        foreach (DBOst t in instr.DBOst.Take(instr.DBOst.Count).ToArray())
                        {
                            instr.DBOst.Remove(t);
                            db.DeleteObject(t);
                        }

                        instr.DBFees.Load();
                        foreach (DBFees t in instr.DBFees.Take(instr.DBFees.Count).ToArray())
                        {
                            instr.DBFees.Remove(t);
                            db.DeleteObject(t);
                        }

                        instr.DBRulesReference.Load();
                    }

                    if (instr.Name != String.Empty)
                    {
                        instr.DBExchange = db.DBExchange.Where(p => p.ExchangeName.CompareTo(i.ExchangeName) == 0).FirstOrDefault();
                        instr.Refinancing = i.Refinancing;
                        instr.Classe = i.Class.ToString();
                        instr.Country = i.Country.ToString();
                        instr.DBCurrency = db.DBCurrency.Where(p => p.Ticker.CompareTo(i.Currency) == 0).FirstOrDefault();
                        instr.ISIN = i.ISIN ?? String.Empty;
                        instr.BelongToIndex = i.BelongToIndex ?? String.Empty;
                        instr.Capitalization = i.Capitalization ?? 0;
                        instr.Maturity = i.Maturity;
                        instr.Name = i.Name;
                        instr.Sector = i.Sectors.ToString();
                        instr.Type = i.Type.ToString();
                        instr.Refinancing = i.Refinancing;
                        instr.DBRules.isBuyAllowed = i.isBuyAllowed;
                        instr.DBRules.isHardToBorrow = i.isHardToBorrow;
                        instr.DBRules.isSellAllowed = i.isSellAllowed;
                        instr.DBRules.isShortable = i.isShortable;
                        instr.DBRules.MaxNominalOrder = i.MaxNominalOrder;
                        instr.DBRules.TickValue = i.TickValue;
                        instr.DBRules.Tick = i.Tick;
                        instr.DBRules.DBInstruments = instr;

                        foreach (Ticker t in i.Tickers)
                            db.AddToDBTickers(new DBTickers
                            {
                                ID = Guid.NewGuid(),
                                DBInstruments = instr,
                                Provider = t.Provider,
                                Ticker = t.Ric
                            });

                        foreach (Ost t in i.Osts)
                            db.AddToDBOst(new DBOst
                            {
                                ID = Guid.NewGuid(),
                                DBInstruments = instr,
                                OstDate = t.Date,
                                OstEvent = t.OstEvent,
                                OstValue = t.OstValue
                            });

                        foreach (Fees t in i.Fees)
                            db.AddToDBFees(new DBFees
                            {
                                ID = Guid.NewGuid(),
                                Borrow = t.Borrow,
                                Broker = t.Broker,
                                CancelFees = t.CancelFee,
                                Crncy = t.Crncy,
                                DBInstruments = instr,
                                ExecFees = t.ExecFee,
                                IntradayInitialMargin = t.IntradayInitialMargin,
                                IntradayMaintenanceMargin = t.IntradayMaintenanceMargin,
                                isPercentageFees = t.isPercentageFees,
                                Lend = t.Lending,
                                MinExecFee = t.MinExecFee,
                                ModifyFees = t.ModifFee,
                                OvernightInitialMargin = t.OvernightInitialMargin,
                                OvernightMaintenanceMargin = t.OvernightMaintenanceMargin
                            });

                        try
                        {
                            db.AddToDBInstruments(instr);
                        }
                        catch
                        {
                        }

                        db.SaveChanges(true);
                        RefreshDB(db);
                    }
                }
            }
        }
        private void AddItem_Click(object sender, EventArgs e)
        {
            Grid.SelectedObject = new OverridedInstrument();
        }
        private void DeleteItem_Click(object sender, EventArgs e)
        {
            if (RecorderToolStripButton.CheckState != CheckState.Checked && InstrumentsDataGridView.Rows.Count > 0)
            {
                if (InstrumentsDataGridView.CurrentRow != null)
                {
                    using (var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()))
                    {
                        var id = ((DBInstruments) InstrumentsDataGridView.CurrentRow.DataBoundItem).ID;
                        db.DeleteObject(db.DBInstruments.Where(u => u.ID == id).FirstOrDefault());
                        db.SaveChanges(true);
                        RefreshDB(db);
                    }
                }
            }
            else
                StatusStrip.Text = " Cannot Delete an Instrument While Recording. Stop Recording First.";
        }
        private void InstrumentsDataGridViewCellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0 && e.ColumnIndex == 0)
            {
                DataGridViewRow dgvr = ((DataGridView) sender).Rows[e.RowIndex];
                if ((bool) dgvr.Cells[0].FormattedValue)
                    dgvr.Cells[0].Value = 0;
                else
                    dgvr.Cells[0].Value = 1;
            }
        }
        private void InstrumentsDataGridViewColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (check_Status)
            {
                for (int i = 0; i < InstrumentsDataGridView.RowCount; i++)
                    InstrumentsDataGridView.Rows[i].Cells[0].Value = false;
                check_Status = false;
            }
            else
            {
                for (int i = 0; i < InstrumentsDataGridView.RowCount; i++)
                    InstrumentsDataGridView.Rows[i].Cells[0].Value = true;
                check_Status = true;
            }
        }
        private void InstrumentsDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > -1)
            {
                try
                {
                    var id = ((DBInstruments)InstrumentsDataGridView.CurrentRow.DataBoundItem).ID;
                     using (var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()))
                     {

                         var dbi = db.DBInstruments.Where(u => u.ID == id).FirstOrDefault();
                         dbi.DBCurrencyReference.Load();
                         dbi.DBExchangeReference.Load();
                         dbi.DBTickers.Load();
                         dbi.DBOst.Load();
                         dbi.DBFees.Load();
                         dbi.DBRulesReference.Load(); // ref null why ?
                         dbi.DBRules = db.DBRules.Where(u => u.DBInstruments.ID == dbi.ID).FirstOrDefault();

                         var i = new OverridedInstrument
                         {
                             ID = dbi.ID,
                             Name = dbi.Name,
                             Type = (InstrumentType)Enum.Parse(typeof(InstrumentType), dbi.Type),
                             Class = (AssetClass)Enum.Parse(typeof(AssetClass), dbi.Classe),
                             Sectors = (Sectors)Enum.Parse(typeof(Sectors), dbi.Sector),
                             ISIN = dbi.ISIN,
                             Country = (Countries)Enum.Parse(typeof(Countries), dbi.Country.ToUpper()),
                             Capitalization = (int)dbi.Capitalization,
                             BelongToIndex = dbi.BelongToIndex,
                             Maturity = dbi.Maturity,
                             Currency = dbi.DBCurrency.Ticker,
                             ExchangeName = dbi.DBExchange.ExchangeName,
                             Refinancing = dbi.Refinancing ?? 0,
                             isBuyAllowed = dbi.DBRules.isBuyAllowed,
                             isHardToBorrow = dbi.DBRules.isHardToBorrow,
                             isSellAllowed = dbi.DBRules.isSellAllowed,
                             isShortable = dbi.DBRules.isShortable,
                             MaxNominalOrder = (int)dbi.DBRules.MaxNominalOrder,
                             TickValue = (double)dbi.DBRules.TickValue,
                             Tick = (double)dbi.DBRules.Tick
                         };

                         foreach (DBTickers t in dbi.DBTickers.Take(dbi.DBTickers.Count))
                             i.AddTicker(t.Provider, t.Ticker);

                         foreach (DBOst t in dbi.DBOst.Take(dbi.DBOst.Count))
                             i.AddOst(t.OstDate, t.OstEvent, t.OstValue);


                         foreach (DBFees t in dbi.DBFees.Take(dbi.DBFees.Count))
                         {
                             i.AddFees(new OverridedFees
                                           (
                                           (double)t.Borrow,
                                           t.Broker,
                                           (double)t.CancelFees,
                                           t.Crncy,
                                           (double)t.ExecFees,
                                           (double)t.IntradayInitialMargin,
                                           (double)t.IntradayMaintenanceMargin,
                                           (bool)t.isPercentageFees,
                                           (double)t.Lend,
                                           (double)t.MinExecFee,
                                           (double)t.ModifyFees,
                                           (double)t.OvernightInitialMargin,
                                           (double)t.OvernightMaintenanceMargin));
                         }

                         Grid.SelectedObject = i;
                     }
                }
                catch (Exception ex)
                {
                    Remoter.Trace(new FireMessageEventArgs(ex, Time.Now.TimeOfDay));
                }
            }
        }
        private void Update_Click(object sender, EventArgs e)
        {
            SetInstrument(Grid);
        }
        private void StockMarketstoolStripButton_Click(object sender, EventArgs e)
        {
            var a = new MarketsForm();
            a.Show();
        }
        private void CurrenciesStripButton_Click(object sender, EventArgs e)
        {
            var a = new CurrenciesForm();
            a.Show();
        }
        private void ImportExceltoolStripButton_Click(object sender, EventArgs e)
        {
            openFileDialog.Title = " Import Fees Strucutre From Excel";
            openFileDialog.Filter = "Excel (*.xls)|*.xls|Excel 2007 (*.xlsx)|*.xlsx";
            openFileDialog.InitialDirectory = GlobalVariables.RESOURCES;

            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                Thread.CurrentThread.CurrentCulture = GlobalVariables.SystemathicsCulture();
                var app = new Application();
                var workbook = app.Workbooks.Open(openFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                       Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                       Type.Missing, Type.Missing);

                DeleteDBtoolStripButton_Click(this, new EventArgs());

                var instrumentsworksheet = (Worksheet)workbook.Sheets["Instruments"];
                if(instrumentsworksheet != null)
                    ImportInstrumentSheet(instrumentsworksheet);


                var tickersworksheet = (Worksheet)workbook.Sheets["Tickers"];
                if(tickersworksheet != null)
                    ImportTickersSheet(tickersworksheet);

                var feesworksheet = (Worksheet)workbook.Sheets["Fees"];
                if(feesworksheet != null)
                    ImportFeesSheet(feesworksheet);

                RefreshDB(new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()));

                app.Workbooks.Close();
                app.Quit();

                GC.GetTotalMemory(true);
                Marshal.ReleaseComObject(instrumentsworksheet);
                Marshal.ReleaseComObject(tickersworksheet);
                Marshal.ReleaseComObject(feesworksheet);
                Marshal.ReleaseComObject(app.Workbooks);
                Marshal.ReleaseComObject(app);
                GC.WaitForPendingFinalizers();
                GC.GetTotalMemory(false);
            }
        }
        private void ImportInstrumentSheet(Worksheet worksheet)
        {
            var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString());
            if (db.DBExchange.Count() > 0 && db.DBCurrency.Count() > 0)
            {
                    var added = 0;
                    if(worksheet == null)
                        return;
                    var excelRange = worksheet.UsedRange;
                    try
                    {
                        var valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
                        int rowCount = valueArray.GetLength(0);
                        int columnCount = valueArray.GetLength(1);

                        for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                            if(valueArray[rowIndex, 1] == null)
                                rowCount = rowIndex - 1;

                        var iArray = new DBInstruments[rowCount - 1];
                        for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                            iArray[rowIndex - 2] = new DBInstruments { DBRules = new DBRules { ID = Guid.NewGuid() } };

                        for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++)
                        {
                            if (valueArray[1, columnIndex] != null)
                            {
                                string Name = valueArray[1, columnIndex].ToString();

                                if (Name == "ID")
                                {
                                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                                    {
                                        if (valueArray[rowIndex, columnIndex] != null)
                                        {
                                            var g = new Guid(valueArray[rowIndex, columnIndex].ToString());
                                            if (!db.DBInstruments.Where(u => u.ID == g).Any())
                                                iArray[rowIndex - 2].ID = new Guid(valueArray[rowIndex, columnIndex].ToString());
                                            else
                                            {
                                                Remoter.Trace(new FireMessageEventArgs(g + " Already exist in the DataBase",Time.Now.TimeOfDay));
                                                return;
                                            }
                                        }
                                        else
                                        {
                                            Remoter.Trace(new FireMessageEventArgs("Unknown Id - row: " + rowIndex,Time.Now.TimeOfDay));
                                            return;
                                        }
                                    }
                                }
                                else
                                {
                                    if (Name == "BelongToIndex")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                            iArray[RowIndex - 2].BelongToIndex =
                                                ((string) valueArray[RowIndex, columnIndex] == null)
                                                    ? String.Empty
                                                    : valueArray[RowIndex, columnIndex].ToString();
                                    }
                                    else if (Name == "Capitalization")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            var result = long.MinValue;
                                            if (
                                                long.TryParse(
                                                    valueArray[RowIndex, columnIndex].ToString().Replace(",", ""),
                                                    out result))
                                                iArray[RowIndex - 2].Capitalization = result;
                                            else
                                            {
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Error Capitalization - Excel row: " + RowIndex,
                                                        Time.Now.TimeOfDay));
                                                return;
                                            }
                                        }
                                    }
                                    else if (Name == "Classe")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            if (valueArray[RowIndex, columnIndex] != null)
                                            {
                                                var x = valueArray[RowIndex, columnIndex].ToString();
                                                if (Enum.IsDefined(typeof (AssetClass), x))
                                                    iArray[RowIndex - 2].Classe =
                                                        valueArray[RowIndex, columnIndex].ToString();
                                                else
                                                {
                                                    Remoter.Trace(
                                                        new FireMessageEventArgs(
                                                            "Unknown Asset Class - Excel row: " + RowIndex,
                                                            Time.Now.TimeOfDay));
                                                    return;
                                                }
                                            }
                                        }
                                    }
                                    else if (Name == "Sector")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            if (valueArray[RowIndex, columnIndex] != null)
                                            {
                                                var x = valueArray[RowIndex, columnIndex].ToString();
                                                if (Enum.IsDefined(typeof (Sectors), x))
                                                    iArray[RowIndex - 2].Sector =
                                                        valueArray[RowIndex, columnIndex].ToString();
                                                else
                                                {
                                                    Remoter.Trace(
                                                        new FireMessageEventArgs(
                                                            "Unknown Sector - Excel row: " + RowIndex,
                                                            Time.Now.TimeOfDay));
                                                    return;
                                                }
                                            }
                                        }
                                    }
                                    else if (Name == "Type")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            if (valueArray[RowIndex, columnIndex] != null)
                                            {
                                                var x = valueArray[RowIndex, columnIndex].ToString();
                                                if (Enum.IsDefined(typeof (InstrumentType), x))
                                                    iArray[RowIndex - 2].Type =
                                                        valueArray[RowIndex, columnIndex].ToString();
                                                else
                                                {
                                                    Remoter.Trace(
                                                        new FireMessageEventArgs(
                                                            "Unknown Type - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                                    return;
                                                }
                                            }
                                        }
                                    }
                                    else if (Name == "Country")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            if ((string) valueArray[RowIndex, columnIndex] != null)
                                                iArray[RowIndex - 2].Country = valueArray[RowIndex, columnIndex].ToString().ToUpper();
                                            else
                                            {
                                                Remoter.Trace(
                                                    new FireMessageEventArgs("Error Country - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                                return;
                                            }
                                        }
                                    }
                                    else if (Name == "Currency")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            string curTicker = valueArray[RowIndex, columnIndex].ToString();
                                            var curr = db.DBCurrency.Where(o => o.Ticker == curTicker).FirstOrDefault();
                                            if (curr != null)
                                                iArray[RowIndex - 2].DBCurrency = curr;
                                            else
                                            {
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Unknown Currency - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                                return;
                                            }
                                        }
                                    }
                                    else if (Name == "Exchange")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            string exc = valueArray[RowIndex, columnIndex].ToString();
                                            var exch = db.DBExchange.Where(p => p.Exchange == exc).FirstOrDefault();
                                            if (exch != null)
                                                iArray[RowIndex - 2].DBExchange = exch;
                                            else
                                            {
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Unknown Exchange - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                                return;
                                            }
                                        }
                                    }
                                    else if (Name == "ISIN")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                            iArray[RowIndex - 2].ISIN = ((string) valueArray[RowIndex, columnIndex] ==
                                                                         null)
                                                                            ? String.Empty
                                                                            : valueArray[RowIndex, columnIndex].ToString
                                                                                  ();
                                    }
                                    else if (Name == "Maturity")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            if (valueArray[RowIndex, columnIndex] != null)
                                                iArray[RowIndex - 2].Maturity =
                                                    DateTime.Parse(valueArray[RowIndex, columnIndex].ToString(),
                                                                   GlobalVariables.SystemathicsCulture());
                                        }
                                    }
                                    else if (Name == "Name")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            if ((string) valueArray[RowIndex, columnIndex] != null)
                                                iArray[RowIndex - 2].Name = valueArray[RowIndex, columnIndex].ToString();
                                            else
                                            {
                                                Remoter.Trace(new FireMessageEventArgs("Error Name row: " + RowIndex,
                                                                                       Time.Now.TimeOfDay));
                                                return;
                                            }
                                        }
                                    }
                                    else if (Name == "isBuyAllowed")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            var result = false;
                                            if (bool.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                                iArray[RowIndex - 2].DBRules.isBuyAllowed = result;
                                            else
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Error isBuyAllowed - Excel row: " + RowIndex,
                                                        Time.Now.TimeOfDay));
                                        }
                                    }
                                    else if (Name == "isHardToBorrow")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            var result = false;
                                            if (bool.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                                iArray[RowIndex - 2].DBRules.isHardToBorrow = result;
                                            else
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Error isHardToBorrow - Excel row: " + RowIndex,
                                                        Time.Now.TimeOfDay));
                                        }
                                    }
                                    else if (Name == "isSellAllowed")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            var result = false;
                                            if (bool.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                                iArray[RowIndex - 2].DBRules.isSellAllowed = result;
                                            else
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Error isSellAllowed - Excel row: " + RowIndex,
                                                        Time.Now.TimeOfDay));
                                        }
                                    }
                                    else if (Name == "isShortable")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            var result = false;
                                            if (bool.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                                iArray[RowIndex - 2].DBRules.isShortable = result;
                                            else
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Error isShortable - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                        }
                                    }
                                    else if (Name == "Max Nominal Order")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            var result = long.MinValue;
                                            if (long.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                                iArray[RowIndex - 2].DBRules.MaxNominalOrder = result;
                                            else
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Error MaxNominalOrder - Excel row: " + RowIndex,
                                                        Time.Now.TimeOfDay));
                                        }
                                    }
                                    else if (Name == "Tick")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            var result = double.NaN;
                                            if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                                iArray[RowIndex - 2].DBRules.Tick = result;
                                            else
                                                Remoter.Trace(
                                                    new FireMessageEventArgs("Error Tick - Excel row: " + RowIndex,
                                                                             Time.Now.TimeOfDay));
                                        }
                                    }
                                    else if (Name == "TickValue")
                                    {
                                        for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                        {
                                            var result = double.NaN;
                                            if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                                iArray[RowIndex - 2].DBRules.TickValue = result;
                                            else
                                                Remoter.Trace(
                                                    new FireMessageEventArgs(
                                                        "Error TickValue - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                        }
                                    }
                                }
                            }
                        }
                        foreach (DBInstruments dbInstrument in iArray)
                        {
                            if (dbInstrument.ID != Guid.Empty)
                            {
                                var id = dbInstrument.ID;
                                if (!db.DBInstruments.Where(u => u.ID == id).Any())
                                {
                                    try
                                    {
                                        db.AddToDBInstruments(dbInstrument);
                                        added++;
                                    }
                                    catch(Exception ex)
                                    {
                                        added--;
                                        Remoter.Trace(new FireMessageEventArgs(ex, DateTime.Now.TimeOfDay));
                                    }
                                }
                                else
                                    Remoter.Trace(new FireMessageEventArgs("This Fee Already Exist in DB", DateTime.Now.TimeOfDay));
                            }
                        }

                        Remoter.Trace(new FireMessageEventArgs(added + " Instruments Added to DB", DateTime.Now.TimeOfDay));
                        if(added > 0)
                            db.SaveChanges(true);
                        db.Dispose();
                    }
                    catch (Exception ex)
                    {
                        Remoter.Trace(new FireMessageEventArgs(ex, DateTime.Now.TimeOfDay));
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(excelRange);
                    }
            }
            else
                Remoter.Trace(new FireMessageEventArgs("Exchange or Currency Missing in DB", Time.Now.TimeOfDay));

        }
        private void ImportFeesSheet(Worksheet worksheet)
        {
            var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString());
            if (db.DBInstruments.Count() > 0)
            {
                try
                {
                    var added = 0;
                    Thread.CurrentThread.CurrentCulture = GlobalVariables.SystemathicsCulture();

                    if (worksheet == null)
                        return;

                    Range excelRange = worksheet.UsedRange;
                    var valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
                    int rowCount = valueArray.GetLength(0);
                    int columnCount = valueArray.GetLength(1);

                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                        if (valueArray[rowIndex, 1] == null)
                            rowCount = rowIndex - 1;

                    var iArray = new DBFees[rowCount - 1];
                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                        iArray[rowIndex - 2] = new DBFees { ID = Guid.NewGuid() };

                    for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++)
                    {
                        if (valueArray[1, columnIndex] != null)
                        {
                            string Name = valueArray[1, columnIndex].ToString();
                            if (Name == "Broker")
                            {
                                for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                                {
                                    if (valueArray[rowIndex, columnIndex] != null) //                                            if (!db.DBInstruments.Where(u => u.ID == g).Any())
                                        iArray[rowIndex - 2].Broker = valueArray[rowIndex, columnIndex].ToString();
                                    else
                                    {
                                        Remoter.Trace(new FireMessageEventArgs("Unknown Broker - row: " + rowIndex, Time.Now.TimeOfDay));
                                        return;
                                    }
                                }
                            }
                            else
                            {
                                if (Name == "Name")
                                {
                                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                                    {
                                        var name = valueArray[rowIndex, columnIndex];
                                        if (name != null) // Referencing Non-Scalar Closures
                                        {
                                            var query = db.DBInstruments.Where(delegate(DBInstruments t){return t.Name == (String)name;}).AsQueryable();
                                           if(query.Any())
                                               iArray[rowIndex - 2].DBInstruments = query.FirstOrDefault();
                                            else
                                            {
                                                Remoter.Trace(new FireMessageEventArgs("Unknown Instrument Name - Excel row: " + rowIndex, Time.Now.TimeOfDay));
                                                return;
                                            }
                                        }
                                    }
                                }
                                else if (Name == "isPercentageFees")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = false;
                                        if (bool.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].isPercentageFees = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error isPercentageFees - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "ExecFees")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].ExecFees = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error ExecFees - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "MinExecFees")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].MinExecFee = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error MinExecFee - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "ModifyFees")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].ModifyFees = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error ModifyFees - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "CancelFees")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].CancelFees = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error CancelFees - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "Borrow")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].Borrow = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error Borrow - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "Lend")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].Lend = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error Lend - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "Crncy")
                                {
                                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                                    {
                                        var name = valueArray[rowIndex, columnIndex];
                                        if (name != null) 
                                        {
                                            var query = db.DBCurrency.Where(delegate(DBCurrency t) { return t.Ticker == (String)name; }).AsQueryable();
                                            try
                                            {
                                                iArray[rowIndex - 2].Crncy = query.FirstOrDefault().Ticker;
                                            }
                                            catch(Exception ex)
                                            {
                                                Remoter.Trace(new FireMessageEventArgs("Unknown Currency Name - Excel row: " + rowIndex, Time.Now.TimeOfDay));
                                                return;
                                            }
                                        }
                                    }
                                }
                                else if (Name == "Intraday Initial Margin")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if(double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].IntradayInitialMargin = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error IntradayInitialMargin - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "Intraday Maintenance Margin")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].IntradayMaintenanceMargin = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error IntradayMaintenanceMargin - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "Overnight Initial Margin")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].OvernightInitialMargin = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error OvernightInitialMargin - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                                else if (Name == "Overnight Maintenance Margin")
                                {
                                    for (int RowIndex = 2; RowIndex <= rowCount; RowIndex++)
                                    {
                                        var result = double.NaN;
                                        if (double.TryParse(valueArray[RowIndex, columnIndex].ToString(), out result))
                                            iArray[RowIndex - 2].OvernightMaintenanceMargin = result;
                                        else
                                            Remoter.Trace(new FireMessageEventArgs("Error OvernightMaintenanceMargin - Excel row: " + RowIndex, Time.Now.TimeOfDay));
                                    }
                                }
                            }
                        }
                    }

                    foreach (DBFees dbf in iArray)
                    {
                        if (dbf.ID != Guid.Empty)
                        {
                            DBFees fee = dbf;
                            if (!db.DBFees.Where(delegate(DBFees t) { t.DBInstrumentsReference.Load();
                                                                                return
                                                                                (t.Broker == fee.Broker &&
                                                                                t.DBInstruments.Name ==
                                                                                fee.DBInstruments.Name); }
                                                                                ).Any())
                            {
                                try
                                {
                                    db.AddToDBFees(dbf);
                                    added++;
                                }
                                catch(Exception ex)
                                {
                                    added--;
                                    Remoter.Trace(new FireMessageEventArgs(ex, DateTime.Now.TimeOfDay));
                                }
                            }
                            else
                                Remoter.Trace(new FireMessageEventArgs("This Fee Already Exist in DB", DateTime.Now.TimeOfDay));

                            }
                        }

                    Remoter.Trace(new FireMessageEventArgs(added + " Fees Added to DB on " + iArray.Count() + " presents Fees lines in Excel Sheet.", DateTime.Now.TimeOfDay));
                    if (added > 0)
                        db.SaveChanges(true);
                    db.Dispose();
                    
                }
                catch (Exception ex)
                {
                    Remoter.Trace(new FireMessageEventArgs(ex, DateTime.Now.TimeOfDay));
                }
            }
            else
                Remoter.Trace(new FireMessageEventArgs("There is no instruments in DB, Fill DBInstruments first", Time.Now.TimeOfDay));
        }
        private void ImportTickersSheet(Worksheet worksheet)
        {
            var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString());
            if (db.DBInstruments.Count() > 0)
            {
                try
                {
                    var added = 0;
                    if (worksheet == null)
                        return;

                    Range excelRange = worksheet.UsedRange;
                    var valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
                    int rowCount = valueArray.GetLength(0);
                    int columnCount = valueArray.GetLength(1);

                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                        if (valueArray[rowIndex, 1] == null)
                            rowCount = rowIndex - 1;

                    var iArray = new DBTickers[rowCount - 1];
                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                        iArray[rowIndex - 2] = new DBTickers { ID = Guid.NewGuid() };

                    for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++)
                    {
                        if (valueArray[1, columnIndex] != null)
                        {
                            string Name = valueArray[1, columnIndex].ToString();
                            if (Name == "Instrument")
                            {
                                for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                                {
                                    if (valueArray[rowIndex, columnIndex] != null) //                                            
                                    {
                                        var id = new Guid(valueArray[rowIndex, columnIndex].ToString());
                                        iArray[rowIndex - 2].DBInstruments = db.DBInstruments.Where(u => u.ID == id).FirstOrDefault();
                                    }
                                    else
                                    {
                                        Remoter.Trace(new FireMessageEventArgs("Unknown Instrument ID - row:DBInstruments " + rowIndex, Time.Now.TimeOfDay));
                                        return;
                                    }
                                }
                            }
                            else
                            {
                                if (Name == "Provider")
                                {
                                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                                    {
                                        var provider = valueArray[rowIndex, columnIndex];
                                        if (provider != null) // Referencing Non-Scalar Closures
                                            iArray[rowIndex - 2].Provider = provider.ToString();
                                    }
                                }
                                else if (Name == "Ticker")
                                {
                                    for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
                                    {
                                        var ticker = valueArray[rowIndex, columnIndex];
                                        if (ticker != null) // Referencing Non-Scalar Closures
                                        {
                                            iArray[rowIndex - 2].Ticker = ticker.ToString();
                                        }
                                    }
                                }
                            }
                        }
                    }

                    foreach (var dbt in iArray)
                    {
                        if (dbt.ID != Guid.Empty)
                        {
                            var dbticker = dbt;
                            if (!db.DBTickers.Where(delegate(DBTickers t){return (t.Provider == dbticker.Provider && t.Ticker == dbticker.Ticker);}).Any())
                            {
                                try
                                {
                                    db.AddToDBTickers(dbt);
                                    added++;
                                }
                                catch (Exception ex)
                                {
                                    added--;
                                    Remoter.Trace(new FireMessageEventArgs(ex, DateTime.Now.TimeOfDay));
                                }
                            }
                            else
                                Remoter.Trace(new FireMessageEventArgs("This Tickers Already Exist in DB", DateTime.Now.TimeOfDay));

                        }
                    }

                    Remoter.Trace(new FireMessageEventArgs(added + " Tickers Added to DB on " + iArray.Count() + " presents Ticker lines in Excel Sheet.", DateTime.Now.TimeOfDay));
                    if (added > 0)
                        db.SaveChanges(true);
                    db.Dispose();

                }
                catch (Exception ex)
                {
                    Remoter.Trace(new FireMessageEventArgs(ex, DateTime.Now.TimeOfDay));
                }
            }
            else
                Remoter.Trace(new FireMessageEventArgs("There is no instruments in DB, Fill DBInstruments first", Time.Now.TimeOfDay));
        }
        private void DeleteDBtoolStripButton_Click(object sender, EventArgs e)
        {
            using(var db = new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()))
            {
                //Don't Delete Exchanges or Currencies
                foreach (var u in db.DBInstruments.Select(i => i))
                    db.DeleteObject(u);

                foreach (var u in db.DBTickers.Select(i => i))
                    db.DeleteObject(u);

                foreach (var u in db.DBStrategies.Select(i => i))
                    db.DeleteObject(u);

                foreach (var u in db.DBRules.Select(i => i))
                    db.DeleteObject(u);

                foreach (var u in db.DBOst.Select(i => i))
                    db.DeleteObject(u);

                foreach (var u in db.DBOrderStatus.Select(i => i))
                    db.DeleteObject(u);

                foreach (var u in db.DBOrder.Select(i => i))
                    db.DeleteObject(u);

                foreach (var u in db.DBExecutions.Select(i => i))
                    db.DeleteObject(u);

                foreach (var u in db.DBFees.Select(i => i))
                    db.DeleteObject(u);

                db.SaveChanges(true);
                db.AcceptAllChanges();

                RefreshDB(db);
                Grid.SelectedObject = null;
            }
        }

        private void RefreshToolStripButton_Click(object sender, EventArgs e)
        {
            RefreshDB(new SystemathicsEntities(GlobalVariables.EntitiesConnectionString()));
        }

        private void RefreshDB(SystemathicsEntities db)
        {
            db.DBTickers.Execute(MergeOption.OverwriteChanges);
            db.DBStrategies.Execute(MergeOption.OverwriteChanges);
            db.DBRules.Execute(MergeOption.OverwriteChanges);
            db.DBOst.Execute(MergeOption.OverwriteChanges);
            db.DBOrderStatus.Execute(MergeOption.OverwriteChanges);
            db.DBOrder.Execute(MergeOption.OverwriteChanges);
            db.DBExecutions.Execute(MergeOption.OverwriteChanges);
            db.DBFees.Execute(MergeOption.OverwriteChanges);
            dBInstrumentsBindingSource.DataSource = db.DBInstruments.Execute(MergeOption.OverwriteChanges);
        }
    }
}